#include "sql.h"

SQL* SQL::sqlhand = nullptr;
SQL::SQL(QObject *parent) : QObject(parent)
{

}

/****************************************************/
void SQL::createRegInfoTable()
{
    if(database.open())
    {
        QStringList tablelist = database.tables();
        QSqlQuery* sqlquery = new QSqlQuery(database);
        if(!tablelist.contains("RegInfo_table"))
        {
            QString creatusers = "CREATE TABLE RegInfo_table                                      \
                    (                                                                             \
                      AccountID      CHAR(10)  NOT NULL,                                          \
                      Password       CHAR(12)  NOT NULL,                                          \
                      PhoneNumber    CHAR(11)  NOT NULL,                                          \
                      NickName       CHAR(20),                                                    \
                      CONSTRAINT RegInfo_PK PRIMARY KEY (AccountID),                              \
                      CONSTRAINT RegInfo_CK_PN_LEN  CHECK (LENGTH(PhoneNumber) = 11)              \
                    );";
            if(sqlquery->prepare(creatusers))
            {
                if(sqlquery->exec())
                {
                    qDebug() << "Create RegInfo_table OK";
                }
            }
            else
            {
                qDebug() << "create RegInfo_table command error !";
                qDebug() << sqlquery->lastError();
            }
        }
        else
        {
            qDebug() << "RegInfo_table already exit !";
        }
        delete sqlquery;
        database.close();
    }
    else
    {
        qDebug() << "database open failed !";
    }
}

void SQL::createAccountIPTable()
{
    if(database.open())
    {
        QStringList tablelist = database.tables();
        QSqlQuery* sqlquery = new QSqlQuery(database);
        if(!tablelist.contains("AccountIP_table"))
        {
            QString creatusers = "CREATE TABLE AccountIP_table                                             \
                    (                                                                                      \
                      AccountID      CHAR(10)  NOT NULL,                                                   \
                      IP             CHAR(25)  NOT NULL,                                                   \
                      CONSTRAINT AccountIP_PK PRIMARY KEY (AccountID, IP),                                 \
                      CONSTRAINT AccountIP_FK FOREIGN KEY (AccountID) REFERENCES RegInfo_table (AccountID) \
                    );";
            if(sqlquery->prepare(creatusers))
            {
                if(sqlquery->exec())
                {
                    qDebug() << "Create AccountIP_table OK";
                }
            }
            else
            {
                qDebug() << "create AccountIP_table command error !";
                qDebug() << sqlquery->lastError();
            }
        }
        else
        {
            qDebug() << "AccountIP_table already exit !";
        }
        delete sqlquery;
        database.close();
    }
    else
    {
        qDebug() << "database open failed !";
    }
}

void SQL::createGroupChatTable()
{
    if(database.open())
    {
        QStringList tablelist = database.tables();
        QSqlQuery* sqlquery = new QSqlQuery(database);
        if(!tablelist.contains("GroupChat_table"))
        {
            QString creatusers = "CREATE TABLE GroupChat_table                                                 \
                    (                                                                                          \
                      GroupNumber   CHAR(10) NOT NULL,                                                         \
                      IsGroup       INT      NOT NULL,                                                         \
                      GroupLeaderID CHAR(10),                                                                  \
                      GroupName     CHAR(20),                                                                  \
                      CONSTRAINT GroupChat_PK PRIMARY KEY (GroupNumber),                                       \
                      CONSTRAINT GroupChat_FK FOREIGN KEY (GroupLeaderID) REFERENCES RegInfo_table (AccountID) \
                    );";
            if(sqlquery->prepare(creatusers))
            {
                if(sqlquery->exec())
                {
                    qDebug() << "Create GroupChat_table OK";
                }
            }
            else
            {
                qDebug() << "create GroupChat_table command error !";
                qDebug() << sqlquery->lastError();
            }
        }
        else
        {
            qDebug() << "GroupChat_table already exit !";
        }
        delete sqlquery;
        database.close();
    }
    else
    {
        qDebug() << "database open failed !";
    }
}

void SQL::createGroupChatInfoTable()
{
    if(database.open())
    {
        QStringList tablelist = database.tables();
        QSqlQuery* sqlquery = new QSqlQuery(database);
        if(!tablelist.contains("GroupChatInfo_table"))
        {
            QString creatusers = "CREATE TABLE GroupChatInfo_table                                                    \
                    (                                                                                                 \
                      GroupNumber CHAR(10)  NOT NULL,                                                                 \
                      AccountID   CHAR(10)  NOT NULL,                                                                 \
                      CONSTRAINT GroupChatInfo_PK PRIMARY KEY (GroupNumber, AccountID),                               \
                      CONSTRAINT GroupChatInfo_FK FOREIGN KEY (GroupNumber) REFERENCES GroupChat_table (GroupNumber), \
                      CONSTRAINT GroupChatInfo_FK FOREIGN KEY (AccountID)   REFERENCES RegInfo_table    (AccountID)   \
                    );";
            if(sqlquery->prepare(creatusers))
            {
                if(sqlquery->exec())
                {
                    qDebug() << "Create GroupChatInfo_table OK";
                }
            }
            else
            {
                qDebug() << "create GroupChatInfo_table command error !";
                qDebug() << sqlquery->lastError();
            }
        }
        else
        {
            qDebug() << "GroupChatInfo_table already exit !";
        }
        delete sqlquery;
        database.close();
    }
    else
    {
        qDebug() << "database open failed !";
    }
}

void SQL::createHisRecordsTable()
{
    if(database.open())
    {
        QStringList tablelist = database.tables();
        QSqlQuery* sqlquery = new QSqlQuery(database);
        if(!tablelist.contains("HisRecords_table"))
        {
            QString creatusers = "CREATE TABLE HisRecords_table                                                   \
                    (                                                                                             \
                      GroupNumber      CHAR(10)  NOT NULL,                                                        \
                      Time             CHAR(11)  NOT NULL,                                                        \
                      News             CHAR(1000) NOT NULL,                                                       \
                      CONSTRAINT HisRecords_PK PRIMARY KEY (GroupNumber, Time),                                   \
                      CONSTRAINT HisRecords_FK FOREIGN KEY (GroupNumber) REFERENCES GroupChat_table (GroupNumber) \
                    );";
            if(sqlquery->prepare(creatusers))
            {
                if(sqlquery->exec())
                {
                    qDebug() << "Create HisRecords_table OK";
                }
            }
            else
            {
                qDebug() << "create HisRecords_table command error !";
                qDebug() << sqlquery->lastError();
            }
        }
        else
        {
            qDebug() << "HisRecords_table already exit !";
        }
        delete sqlquery;
        database.close();
    }
    else
    {
        qDebug() << "database open failed !";
    }
}

//创建一个好友申请表单，主要用于未处理的好友申请的同步
void SQL::createFriendApplicationTable()
{
    if(database.open())
    {
        QStringList tablelist = database.tables();
        QSqlQuery* sqlquery = new QSqlQuery(database);
        if(!tablelist.contains("FriendApplication_table"))
        {
            QString creatusers = "CREATE TABLE FriendApplication_table                                             \
                    (                                                                                              \
                      AccountID        CHAR(10)  NOT NULL,                                                         \
                      ApplicationInfo  CHAR(100)  NOT NULL,                                                         \
                      CONSTRAINT FriendApplication_PK PRIMARY KEY (AccountID, ApplicationInfo),                    \
                      CONSTRAINT FriendApplication_FK FOREIGN KEY (AccountID) REFERENCES RegInfo_table (AccountID) \
                    );";
            if(sqlquery->prepare(creatusers))
            {
                if(sqlquery->exec())
                {
                    qDebug() << "Create FriendApplication_table OK";
                }
            }
            else
            {
                qDebug() << "create FriendApplication_table command error !";
                qDebug() << sqlquery->lastError();
            }
        }
        else
        {
            qDebug() << "FriendApplication_table already exit !";
        }
        delete sqlquery;
        database.close();
    }
    else
    {
        qDebug() << "database open failed !";
    }
}
/****************************************************/

/****************************************************/
void SQL::insertRegInfoTable(QString _Account, QString _Password, QString _PhoneNumber, QString _NickName)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        QString insertusers = "INSERT INTO RegInfo_table (AccountID, Password, PhoneNumber, NickName) VALUES (?,?,?,?)";
        if(sqlquery->prepare(insertusers))
        {
            sqlquery->addBindValue(QVariant(_Account));
            sqlquery->addBindValue(QVariant(_Password));
            sqlquery->addBindValue(QVariant(_PhoneNumber));
            sqlquery->addBindValue(QVariant(_NickName));
            if(sqlquery->exec())
            {
                qDebug() << "Insert RegInfo_table OK";
            }
            else
            {
                qDebug() << "insertRegInfoTable failed !";
                qDebug() << sqlquery->lastError();
            }
        }
        else
        {
            qDebug() << "Insert RegInfo_table command error !";
            qDebug() << sqlquery->lastError();
        }
        delete sqlquery;
    }
    else
    {
        qDebug() << "database open failed !";
    }
}

void SQL::insertAccountIPTable(QString _Account, QString _IP)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        QString insertusers = "INSERT INTO AccountIP_table (AccountID, IP) VALUES (?,?)";
        if(sqlquery->prepare(insertusers))
        {
            sqlquery->addBindValue(QVariant(_Account));
            sqlquery->addBindValue(QVariant(_IP));
            if(sqlquery->exec())
            {
                qDebug() << "Insert AccountIP_table OK";
            }
            else
            {
                qDebug() << "insertAccountIPTable failed !";
                qDebug() << sqlquery->lastError();
            }
        }
        else
        {
            qDebug() << "Insert AccountIP_table command error !";
            qDebug() << sqlquery->lastError();
        }
        delete sqlquery;
    }
    else
    {
        qDebug() << "database open failed !";
    }
}

void SQL::insertGroupChatTable(QString _GroupNumber, int _IsGroup, QString _GroupLeaderID, QString _GroupName)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        QString insertusers = "INSERT INTO GroupChat_table (GroupNumber, IsGroup, GroupLeaderID, GroupName) VALUES (?,?,?,?)";
        if(sqlquery->prepare(insertusers))
        {
            sqlquery->addBindValue(QVariant(_GroupNumber));
            sqlquery->addBindValue(QVariant(_IsGroup));
            sqlquery->addBindValue(QVariant(_GroupLeaderID));
            sqlquery->addBindValue(QVariant(_GroupName));
            if(sqlquery->exec())
            {
                qDebug() << "Insert GroupChat_table OK";
            }
            else
            {
                qDebug() << "insertGroupChatTable failed !";
                qDebug() << sqlquery->lastError();
            }
        }
        else
        {
            qDebug() << "Insert GroupChat_table command error !";
            qDebug() << sqlquery->lastError();
        }
        delete sqlquery;
    }
    else
    {
        qDebug() << "database open failed !";
    }
}

void SQL::insertGroupChatInfoTable(QString _GroupNumber, QString _Account)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        QString insertusers = "INSERT INTO GroupChatInfo_table (GroupNumber, AccountID) VALUES (?,?)";
        if(sqlquery->prepare(insertusers))
        {
            sqlquery->addBindValue(QVariant(_GroupNumber));
            sqlquery->addBindValue(QVariant(_Account));
            if(sqlquery->exec())
            {
                qDebug() << "Insert GroupChatInfo_table OK";
            }
            else
            {
                qDebug() << "insertGroupChatInfoTable failed !";
                qDebug() << sqlquery->lastError();
            }
        }
        else
        {
            qDebug() << "Insert GroupChatInfo_table command error !";
            qDebug() << sqlquery->lastError();
        }
        delete sqlquery;
    }
    else
    {
        qDebug() << "database open failed !";
    }
}

void SQL::insertHisRecordsTable(QString _GroupNumber, QString _Time, QString _News)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        QString insertusers = "INSERT INTO HisRecords_table (GroupNumber, Time, News) VALUES (?,?,?)";
        if(sqlquery->prepare(insertusers))
        {
            sqlquery->addBindValue(QVariant(_GroupNumber));
            sqlquery->addBindValue(QVariant(_Time));
            sqlquery->addBindValue(QVariant(_News));
            if(sqlquery->exec())
            {
                qDebug() << "Insert HisRecords_table OK";
            }
            else
            {
                qDebug() << "insertHisRecordsTable failed !";
                qDebug() << sqlquery->lastError();
            }
        }
        else
        {
            qDebug() << "Insert HisRecords_table command error !";
            qDebug() << sqlquery->lastError();
        }
        delete sqlquery;
    }
    else
    {
        qDebug() << "database open failed !";
    }
}

void SQL::insertFriendApplication(QString _Account, QString _ApplicationInfo)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        QString insertusers = "INSERT INTO FriendApplication_table (AccountID, ApplicationInfo) VALUES (?,?)";
        if(sqlquery->prepare(insertusers))
        {
            sqlquery->addBindValue(QVariant(_Account));
            sqlquery->addBindValue(QVariant(_ApplicationInfo));
            if(sqlquery->exec())
            {
                qDebug() << "Insert FriendApplication_table OK";
            }
            else
            {
                qDebug() << "insertFriendApplication failed !";
                qDebug() << sqlquery->lastError();
            }
        }
        else
        {
            qDebug() << "Insert FriendApplication_table command error !";
            qDebug() << sqlquery->lastError();
        }
        delete sqlquery;
    }
    else
    {
        qDebug() << "database open failed !";
    }
}
/****************************************************/

/****************************************************/
void SQL::deleteRegInfoTable(QString _Account)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("DELETE FROM RegInfo_table WHERE AccountID = ?;");
        sqlquery->addBindValue(_Account);
        sqlquery->exec();
        delete sqlquery;
    }
}

void SQL::deleteAccountIPTable(QString _Account, QString _IP)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("DELETE FROM AccountIP_table WHERE AccountID = ? AND IP = ?;");
        sqlquery->addBindValue(_Account);
        sqlquery->addBindValue(_IP);
        sqlquery->exec();
        delete sqlquery;
    }
}

void SQL::deleteGroupChatTable(QString _GroupNumber)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("DELETE FROM GroupChat_table WHERE GroupNumber = ?;");
        sqlquery->addBindValue(_GroupNumber);
        sqlquery->exec();
        delete sqlquery;
    }
}

void SQL::deleteGroupChatInfoTable(QString _GroupNumber, QString _Account)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("DELETE FROM GroupChatInfo_table WHERE GroupNumber = ? AND AccountID = ?;");
        sqlquery->addBindValue(_GroupNumber);
        sqlquery->addBindValue(_Account);
        sqlquery->exec();
        delete sqlquery;
    }
}

void SQL::deleteHisRecordsTable(QString _GroupNumber, QString _Time)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("DELETE FROM HisRecords_table WHERE GroupNumber = ? AND Time = ?;");
        sqlquery->addBindValue(_GroupNumber);
        sqlquery->addBindValue(_Time);
        sqlquery->exec();
        delete sqlquery;
    }
}

void SQL::deleteFriendApplication(QString _Account, QString _ApplicationInfo)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("DELETE FROM FriendApplication_table WHERE AccountID = ? AND ApplicationInfo = ?;");
        sqlquery->addBindValue(_Account);
        sqlquery->addBindValue(_ApplicationInfo);
        sqlquery->exec();
        delete sqlquery;
    }
}
/****************************************************/

/****************************************************/
void SQL::deleteAccountIPTableOnAccount(QString _Account)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("DELETE FROM AccountIP_table WHERE AccountID = ?;");
        sqlquery->addBindValue(_Account);
        sqlquery->exec();
        delete sqlquery;
    }
}

void SQL::deleteGroupChatInfoTableOnGroupId(QString _GroupNumber)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("DELETE FROM GroupChatInfo_table WHERE GroupNumber = ?;");
        sqlquery->addBindValue(_GroupNumber);
        sqlquery->exec();
        delete sqlquery;
    }
}

void SQL::deleteGroupChatInfoTableOnAccount(QString _Account)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("DELETE FROM GroupChatInfo_table WHERE AccountID = ?;");
        sqlquery->addBindValue(_Account);
        sqlquery->exec();
        delete sqlquery;
    }
}

void SQL::deleteHisRecordsTableOnGroupId(QString _GroupNumber)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("DELETE FROM HisRecords_table WHERE GroupNumber = ?;");
        sqlquery->addBindValue(_GroupNumber);
        sqlquery->exec();
        delete sqlquery;
    }
}

void SQL::deleteFriendApplicationOnAccount(QString _Account)
{
    if(database.open())
    {
        QSqlQuery* sqlquery = new QSqlQuery(database);
        sqlquery->prepare("DELETE FROM FriendApplication_table WHERE AccountID = ?;");
        sqlquery->addBindValue(_Account);
        sqlquery->exec();
        delete sqlquery;
    }
}
/****************************************************/
